library(tidyverse)
library(readr)
library(scales)

library(ggplot2)
library(gt)
library(gtable)
library(DT)

todo

Insight in the economy of an Massively Multiplayer Online Role-Playing Game using the example of Guild Wars 2

Using the data science lifecycle of plan, data, model and deployment, I want to provide insights into many aspects of data science, programming in R and the world of the online game Guild Wars 2.

data science lifecycle

Plan

Use-Case and Problem

Malcolm Forbes once said, “Money isn’t everything as long as you have enough of it”. Sure, money doesn’t buy happiness, but it can buy a lot of things. This is not only true for the real world, but especially in online games, where micro transactions and payed services have become the new normal.

More and more games give you the choice to invest a lot of time or a lot of money. Both ways you’ll somehow achieve the goal of the game faster. In some games you can even exchange digital goods for real money.

To get to the point, having digital currency in games can save you time, stress and even real money.

There are multiple ways to earn money in games. Often you’ll get on with just playing the game and getting some money from enemies, but for buying the good stuff, one needs a lot more money.

One way may be farming, which means hunting special items in high amounts and selling them to other players. Here’s the question, which items are worth collecting and can be sold for which prices?

Another way is buying items from other players and reselling them with a higher price tag, just like in the real world. Many games have auction houses where those transactions can be made. For this type of income one needs to know what to buy when, which prices are low or high, and when to sell for which amount.

For this project my example of game will be Guild Wars 2. Guild Wars 2 was published in 2012 by NCSoft. There are no monthly fees and the basic game is free to play since 2015. It is a massively multiplayer online role-playing game, meaning a lot of people playing in parallel in an online world. There are over 20.000 items that can be collected and many of them can be sold and bought at the auction house, called the trading post.

trading post

This is a picture of the auction house. There are many different types of items (Armor, Weapons, …)

When selling items, 5% of the cost is a fee for the auction house and gets immediatly taken from your wallet. When buying items, 10% of the price goes to the bank before the rest is delivered to the seller. Because of that it’s important to find items where the buy-to-sell price ratio is the highest.

Buying and selling items this way is called flipping items in Guild Wars speech.

To make one thing clear, there are already a lot of web pages offering this or similar kind of services like GW2Efficiency or GW2TP. This should only be a fun project exploring the R language and data science in general.

Identify variables

For making the most out of our data, we can try to identify some important variables.

  1. For buying low and selling high, we need to know everything about the prices like average, mean and quantiles.
  2. Where is the highest difference between buy and sell prices?
  3. Are there any outliers that are must-buys?
  4. What is the quantity of items on the market?
  5. Are there any special times when people buy/sell?
  6. Investing in one item, what is the return of investment (ROI)?

Define Metrics

Currently I’m really inexperienced with the auction market. I just buy the items I need.

The goal is to earn at least 18 gold and 6 silver just by buying/selling items that the exploration/model rates as highest ROI. This is the current values of 1$.

Data

Aquire data

The data we need can be fetched from a REST API at https://api.guildwars2.com. There is also a good documentation on how to use the API.

We’ll use three endpoint here: 1. Get all items there are in the game

For each item:

  1. Get selling and buying listings from auction house api
  2. Get basic information about the item like name, rarity etc.

At the end we can merge all this data to get a good overview of which items are sold/bought at the auction house. When joining all this together, we get a huge dataset of: - nearly 30.000 items - around 500.000 buy auctions (orders) - over 4.300.000 sell auctions (offers).

There are also APIs where authorization is needed to get information about one’s user profile, but we won’t need this data for this analysis.

The code can be found in the file scrape.Rmd.

One important thing to notice: The api can be called with 1 - 200 items (IDs as query parameters). It is much more performant to call the API in batches of 200 items.

Data ingestion

Load the data from CSV. We don’t want to get that amount of data from the API again and again.

dir <- getwd()
item_list_buys <- read.csv(paste(dir, "/gw2-all-buys.csv", sep = ""))
item_list_sells <- read.csv(paste(dir, "/gw2-all-sells.csv", sep = ""))

To get a quick impressions of the data, let’s have a look at the rough numbers.

This is how the buyer data looks like:

glimpse(item_list_buys)
## Rows: 467,406
## Columns: 10
## $ name         <chr> "Sealed Package of Snowballs", "Sealed Package of Snowbal~
## $ description  <chr> "Open this package to create several snowballs that can h~
## $ type         <chr> "Consumable", "Consumable", "Consumable", "Consumable", "~
## $ rarity       <chr> "Basic", "Basic", "Basic", "Basic", "Basic", "Basic", "Ba~
## $ vendor_value <int> 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, ~
## $ id           <int> 24, 24, 24, 24, 24, 24, 24, 24, 24, 24, 24, 24, 24, 24, 2~
## $ icon         <chr> "https://render.guildwars2.com/file/1D05D1EE04E16E69710E1~
## $ listings     <int> 1, 1, 4, 7, 5, 1, 3, 10, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1,~
## $ unit_price   <int> 85, 81, 80, 77, 76, 75, 74, 72, 68, 67, 66, 65, 64, 63, 6~
## $ quantity     <int> 169, 62, 1000, 1578, 1024, 250, 630, 2365, 250, 250, 38, ~

The seller data has the same structure, just for all the offers:

glimpse(item_list_sells)
## Rows: 4,231,248
## Columns: 10
## $ name         <chr> "Sealed Package of Snowballs", "Sealed Package of Snowbal~
## $ description  <chr> "Open this package to create several snowballs that can h~
## $ type         <chr> "Consumable", "Consumable", "Consumable", "Consumable", "~
## $ rarity       <chr> "Basic", "Basic", "Basic", "Basic", "Basic", "Basic", "Ba~
## $ vendor_value <int> 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, ~
## $ id           <int> 24, 24, 24, 24, 24, 24, 24, 24, 24, 24, 24, 24, 24, 24, 2~
## $ icon         <chr> "https://render.guildwars2.com/file/1D05D1EE04E16E69710E1~
## $ listings     <int> 1, 1, 2, 3, 6, 9, 4, 4, 5, 2, 3, 6, 5, 11, 41, 5, 8, 8, 6~
## $ unit_price   <int> 275, 360, 372, 373, 374, 375, 376, 377, 378, 379, 380, 38~
## $ quantity     <int> 17, 55, 18, 137, 9, 263, 4, 8, 7, 5, 5, 365, 20, 386, 292~
  • Name: This is the name of the item.
  • Description: Some items also have a description giving further information.
  • Type: can be something like armor, consumable, crafting material and so on
  • Rarity: There are 8 types of rarity from very common to very rare.
  • Vendor_value: What a sell to a NPC dealer would yield.
  • Id: Unique ID of the item.
  • Icon: URL to a picture of the item
  • Listings: How many players are offering this item to this price
  • Unit_Price: What the player wants for this item (in copper)
  • Quantity: How many offers of this items there are for this price.

Listings, Unit_Price and Quantity can be read together.

E.g. for the first data entry:

  • There is 1 (listings) player
  • offering 17 (quantity) pieces of this item
  • for 168 (unit_price) copper each.

We’ll learn more about copper shortly.

Compare total items of all buys and sells:

nrow(item_list_buys)
## [1] 467406
nrow(item_list_sells)
## [1] 4231248

Compare unique items of buys and sells:

item_list_buys %>% 
  select(name) %>% 
  drop_na() %>% 
  unique() %>% 
  count() %>% 
  first()
## [1] 18931
item_list_sells %>% 
  select(name) %>% 
  drop_na() %>% 
  unique() %>% 
  count() %>% 
  first()
## [1] 17896

Let’s have a closer look at the data if we need to clean anything.

Data Cleaning

Currency

coin

Just like in the real world, where 100 cents are 1 Euro, the main currency in Guild Wars, coins, are separated into three units.

There is copper, silver and gold. All those values are the same and are automatically calculated by the game.

10.000 copper == 100 silver == 1 gold.

So we need to divide all values by 10.000 to display the price in gold, which is good for expensive goods and when we are searching for the upper prices.

There may be occasions where it makes more sense to look for silver or even copper, but let’s introduce the price as gold first.

df_sells <- item_list_sells %>% 
  mutate(unit_price_gold = unit_price / 10000,
         unit_price_silver = unit_price / 100,
         unit_price_copper = unit_price) %>% 
  select(-unit_price)

df_buys <- item_list_buys %>% 
  mutate(unit_price_gold = unit_price / 10000,
         unit_price_silver = unit_price / 100,
         unit_price_copper = unit_price) %>% 
  select(-unit_price)

Listings and Quantities

In the dataset, there are listings and quantities. Each row is one or more player offering or ordering one type of item for one specific price. We don’t have to care much about listings, as it’s only a subcategory of quantity, so let’s get rid of it.

df_sells <- df_sells %>% 
  select(-listings)

df_buys <- df_buys %>% 
  select(-listings)

Categorical values

Type and Rarity are categorical values with a static set and can therefore be treated as factors.

df_sells <- df_sells %>% 
  mutate(rarity = as.factor(rarity),
         type = as.factor(type))

df_buys <- df_buys %>% 
  mutate(rarity = as.factor(rarity),
         type = as.factor(type))

This will make it easier to use their distinct values

df_types <- data.frame(rep("Type"), levels(df_sells$type))
df_types %>% 
gt(rowname_col = "name") %>%
  tab_header(title = "Types")
Types
rep..Type.. levels.df_sells.type.
Type Armor
Type Back
Type Bag
Type Consumable
Type Container
Type CraftingMaterial
Type Gizmo
Type MiniPet
Type Tool
Type Trinket
Type Trophy
Type UpgradeComponent
Type Weapon
 
df_rarity <- data.frame(rep("Rarity"), levels(df_sells$rarity))
df_rarity %>% 
gt(rowname_col = "name") %>%
  tab_header(title = "Rarity")
Rarity
rep..Rarity.. levels.df_sells.rarity.
Rarity Ascended
Rarity Basic
Rarity Exotic
Rarity Fine
Rarity Legendary
Rarity Masterwork
Rarity Rare

Analyze Data

Item prices

df_sells_group <- df_sells %>% 
  group_by(name) %>% 
  summarise(mean_unit_price = mean(unit_price_gold), 
            mean_vendor_value = mean(vendor_value) / 10000,
            type = unique(type),
            rarity = unique(rarity)) %>% 
  arrange(desc(mean_unit_price))

top5 <- top_n(ungroup(df_sells_group), 5, mean_unit_price)

top5 %>% 
  ggplot() +
  geom_bar(aes(x = name, y = mean_unit_price, fill = rarity), stat="identity") +
  geom_point(aes(x = name, y = mean_vendor_value), fill = "#000000", stat="identity") +
  theme(axis.text.x = element_text(angle = 90, vjust = 0.5, hjust=1)) +
  scale_x_discrete(limits = top5$name) +
  labs(x = "Name", y = "Mean price", title = "Most expensive items")

9999 gold seems to be the maximum possible price for an auction. Vendor prices are so low (copper range), that they appear as nearly zero points in this chart.

Our top item here, the Festive Grymm Svaard, is a cosmetic item that can only be bought with real money and a lot of luck. This is only an offer, so we’ll have to find out if anyone would ever buy this item to this price.

df_buys %>% 
  filter(name == 'Festive Grymm Svaard') %>% 
  select(name, unit_price_gold) %>%
  top_n(5) %>% 
  arrange(desc((unit_price_gold))) %>% 
  gt(rowname_col = "name") %>%
  tab_header(title = "Orders of the Festive Grymm Svaard") %>%
  fmt_number(
    columns = unit_price_gold
  )
## Selecting by unit_price_gold
Orders of the Festive Grymm Svaard
unit_price_gold
Festive Grymm Svaard 300.70
Festive Grymm Svaard 300.70
Festive Grymm Svaard 276.47
Festive Grymm Svaard 76.47
Festive Grymm Svaard 76.47

As we can see, the highest order on this item is 300 gold. That’s pretty much, but far away from 9999.

But who doesn’t want to look that fancy. Cordyceps Festive Grymm Svaard

Rarity of items

There are different rarities in Guild Wars that suggest how often this item will occur in the game:

rarities <- c("Junk", "Basic", "Fine", "Masterwork", "Rare", "Exotic", "Ascended", "Legendary")
colors <- 
  c("Junk" = "#a3aeb9",
    "Basic" = "#000000",
    "Fine" = "#6a9cd9", 
    "Masterwork" = "#17911a", 
    "Rare" = "#f0cd25",
    "Exotic" = "#e9ad1a",
    "Ascended" = "#e64680",
    "Legendary" = "#53238a")

df <- data.frame(rarities)

df %>% 
  ggplot() +
  geom_bar(aes(x = rarities, fill = rarities)) +
  scale_x_discrete(limits = rarities) +
  scale_fill_manual("Rarity", values = colors) + 
  theme(axis.text.y=element_blank(),
        axis.title.y=element_blank(),
        axis.title.x=element_blank())

What is obvious by the names, rarity on the left is in general more common than rarity on the right.

One important information is that a lot of very rare items (Ascended and Legendary) can’t be sold to other players. That’s the reason we see much more Rare and Exotic items being sold for high prices than those really rare ones. Junk is zero because it can only be sold at non-player vendors.

In our dataset, we have the following distribution:

df <- df_sells %>% 
  count(rarity) %>% 
  arrange(desc(n))

df %>% 
  ggplot() +
  geom_bar(aes(x = rarity, y = n, fill = rarity), stat='identity') +
  scale_x_discrete(limits = rarities) +
  scale_fill_manual("Rarity", values = colors)

Types

There are a lot of different types of items. As we can see, Armor, Consumables and Weapons are the most common items.

Here we only see the distribution of items in the game. There are no numbers about sells yet.

This means there are very many different weapon, consumable and armor items in the game, but not that many different crafting materials and upgrade components which seems reasonable.

df <- df_sells %>% 
  distinct(type, name) %>%
  group_by(type) %>% 
  count(type)

df %>% 
  ggplot() +
  geom_bar(aes(x = type, y = n, fill = type), stat='identity') +
  theme(axis.text.x = element_text(angle = 45, hjust=1))

Let’s have a closer look at the type Tinket.

df_sells %>%
  filter(type == "Trinket") %>% 
  group_by(type, rarity) %>% 
  summarise(name = unique(name),
            mean = mean(unit_price_gold),
            icon = min(web_image(icon, height = 50))
            ) %>% 
  drop_na() %>% 
  arrange(desc(mean)) %>%
  gt(rowname_col = "name") %>%
  tab_header(title = "Trinkets", subtitle = "Items with special means") %>%
  fmt_number(
    columns = mean,
    suffixing = "G"
  ) %>% 
  fmt_markdown(
    columns = icon
  ) %>% 
  summary_rows(
    columns = mean,
    fns = list(TOTAL = "sum"),
    formatter = fmt_number
  ) %>%
  tab_footnote(
    footnote = "Prices in gold",
    locations = cells_column_labels(columns = mean)
  ) %>%
  tab_source_note(
    "Based on data from api.guildwars2.com"
  ) %>% 
  tab_options(
    summary_row.background.color = "#ACEACE",
    row_group.background.color = "#FFEFDB",
    table.layout = "auto",
    container.overflow.x = TRUE,
    container.height = px(350)
  )
Trinkets
Items with special means
mean1 icon
Trinket - Basic
White Coral Ring 5.13
Trinket - Fine
Turquoise Copper Amulet 4.23
Tiger's Eye Copper Stud 4.23
Tiger's Eye Copper Ring 4.23
Turquoise Copper Ring 4.23
Malachite Copper Ring 4.23
Garnet Copper Amulet 4.23
Garnet Copper Stud 4.23
Garnet Copper Ring 4.23
Turquoise Copper Stud 4.23
Malachite Copper Stud 4.23
Carnelian Silver Ring 4.23
Peridot Silver Ring 4.23
Carnelian Silver Amulet 4.23
Peridot Silver Amulet 4.23
Carnelian Silver Stud 4.23
Peridot Silver Earring 4.23
Lapis Silver Ring 4.23
Lapis Silver Amulet 4.23
Lapis Silver Earring 4.23
Peridot Gold Ring 4.23
Topaz Gold Ring 4.23
Peridot Gold Amulet 4.23
Topaz Gold Amulet 4.23
Peridot Gold Earring 4.23
Topaz Gold Earring 4.23
Carnelian Gold Band 4.23
Lapis Gold Ring 4.23
Amethyst Gold Ring 4.23
Carnelian Gold Pendant 4.23
Lapis Gold Amulet 4.23
Amethyst Gold Amulet 4.23
Carnelian Gold Earring 4.23
Lapis Gold Earring 4.23
Amethyst Gold Earring 4.23
Amethyst Silver Band 4.23
Pearl Copper Ring 4.23
Pearl Copper Amulet 4.23
Amethyst Silver Pendant 4.23
Pearl Copper Stud 4.23
Amethyst Silver Earring 4.23
Tiger's Eye Copper Amulet 4.23
Malachite Copper Amulet 4.23
Amber Copper Amulet 4.23
Amber Copper Stud 4.23
Amber Copper Ring 4.23
Topaz Silver Band 4.23
Topaz Silver Earring 4.23
Topaz Silver Pendant 4.23
Beryl Platinum Ring 4.23
Chrysocola Platinum Ring 4.23
Emerald Platinum Ring 4.23
Coral Platinum Ring 4.23
Ruby Platinum Ring 4.23
Sapphire Platinum Ring 4.23
Ruby Mithril Ring 4.23
Sapphire Mithril Ring 4.23
Beryl Mithril Ring 4.23
Chrysocola Mithril Ring 4.23
Emerald Mithril Ring 4.23
Coral Mithril Ring 4.23
Beryl Platinum Earring 4.23
Chrysocola Platinum Earring 4.23
Emerald Platinum Earring 4.23
Coral Platinum Earring 4.23
Ruby Platinum Earring 4.23
Sapphire Platinum Earring 4.23
Chrysocola Mithril Earring 4.23
Emerald Mithril Earring 4.23
Beryl Mithril Earring 4.23
Sapphire Mithril Earring 4.23
Ruby Mithril Earring 4.23
Coral Mithril Earring 4.23
Beryl Platinum Amulet 4.23
Chrysocola Platinum Amulet 4.23
Emerald Platinum Amulet 4.23
Coral Platinum Amulet 4.23
Ruby Platinum Amulet 4.23
Sapphire Platinum Amulet 4.23
Ruby Mithril Amulet 4.23
Sapphire Mithril Amulet 4.23
Beryl Mithril Amulet 4.23
Chrysocola Mithril Amulet 4.23
Emerald Mithril Amulet 4.23
Coral Mithril Amulet 4.23
Spinel Silver Ring 4.23
Spinel Gold Ring 4.23
Spinel Silver Earring 4.23
Spinel Gold Earring 4.23
Spinel Silver Amulet 4.23
Spinel Gold Amulet 4.23
Commissar's Manifesto 4.23
Breath of Kralkatorrik 4.23
Symbol of the Inquest 4.23
Rotbeard's Treasure 4.23
Pendant of Arah 4.23
All Seeing 4.23
Star of Dwayna 4.23
Sentinel's Bane 4.23
Circle of Arah 4.23
Balthazar's Band 4.23
Dwayna's Embrace 4.23
Melandru's Bloom 4.23
Signet of Grenth 4.23
Eternal Ice 4.23
Sam 4.23
Ulgoth's Tail 4.23
Rhendak's Signet 4.23
Blue Coral Ring 4.23
Passiflora Mithril Ring 4.23
Passiflora Mithril Earring 4.23
Passiflora Mithril Amulet 4.23
Azurite Mithril Amulet 4.23
Azurite Mithril Earring 4.23
Azurite Mithril Ring 4.23
Sunstone Silver Band 4.23
Sunstone Gold Ring 4.23
Opal Platinum Ring 4.23
Opal Mithril Ring 4.23
Sunstone Silver Earring 4.23
Sunstone Gold Earring 4.23
Opal Platinum Earring 4.23
Opal Mithril Earring 4.23
Sunstone Silver Pendant 4.23
Sunstone Gold Amulet 4.23
Opal Platinum Amulet 4.23
Opal Mithril Amulet 4.23
Trinket - Masterwork
Garnet Copper Ring 2.90
Turquoise Copper Ring 2.90
Malachite Copper Ring 2.90
Tiger's Eye Copper Ring 2.90
Amethyst Silver Band 2.90
Garnet Copper Amulet 2.90
Tiger's Eye Copper Amulet 2.90
Turquoise Copper Stud 2.90
Garnet Copper Stud 2.90
Tiger's Eye Copper Stud 2.90
Malachite Copper Stud 2.90
Amethyst Silver Earring 2.90
Malachite Copper Amulet 2.90
Amethyst Silver Pendant 2.90
Turquoise Copper Amulet 2.90
Ring 2.90
Earring 2.90
Carnelian Silver Ring 2.90
Carnelian Silver Amulet 2.90
Carnelian Silver Stud 2.90
Peridot Silver Ring 2.90
Peridot Silver Pendant 2.90
Peridot Silver Earring 2.90
Carnelian Gold Band 2.90
Amethyst Gold Ring 2.90
Lapis Gold Ring 2.90
Carnelian Gold Pendant 2.90
Amethyst Gold Amulet 2.90
Lapis Gold Amulet 2.90
Carnelian Gold Earring 2.90
Amethyst Gold Earring 2.90
Lapis Gold Earring 2.90
Peridot Gold Ring 2.90
Topaz Gold Ring 2.90
Peridot Gold Amulet 2.90
Topaz Gold Amulet 2.90
Peridot Gold Earring 2.90
Topaz Gold Earring 2.90
Iron Ring 2.90
Pearl Copper Amulet 2.90
Amber Copper Amulet 2.90
Amber Copper Stud 2.90
Pearl Copper Stud 2.90
Pearl Copper Ring 2.90
Amber Copper Ring 2.90
Lapis Silver Ring 2.90
Topaz Silver Band 2.90
Lapis Silver Earring 2.90
Topaz Silver Earring 2.90
Lapis Silver Amulet 2.90
Topaz Silver Pendant 2.90
Ruby Platinum Ring 2.90
Sapphire Platinum Ring 2.90
Beryl Platinum Ring 2.90
Chrysocola Platinum Ring 2.90
Emerald Platinum Ring 2.90
Coral Platinum Ring 2.90
Chrysocola Mithril Ring 2.90
Emerald Mithril Ring 2.90
Beryl Mithril Ring 2.90
Sapphire Mithril Ring 2.90
Ruby Mithril Ring 2.90
Coral Mithril Ring 2.90
Ruby Platinum Earring 2.90
Sapphire Platinum Earring 2.90
Beryl Platinum Earring 2.90
Chrysocola Platinum Earring 2.90
Emerald Platinum Earring 2.90
Coral Platinum Earring 2.90
Chrysocola Mithril Earring 2.90
Sapphire Mithril Earring 2.90
Ruby Mithril Earring 2.90
Coral Mithril Earring 2.90
Emerald Mithril Earring 2.90
Beryl Mithril Earring 2.90
Ruby Platinum Amulet 2.90
Sapphire Platinum Amulet 2.90
Beryl Platinum Amulet 2.90
Chrysocola Platinum Amulet 2.90
Emerald Platinum Amulet 2.90
Coral Platinum Amulet 2.90
Chrysocola Mithril Amulet 2.90
Emerald Mithril Amulet 2.90
Beryl Mithril Amulet 2.90
Sapphire Mithril Amulet 2.90
Ruby Mithril Amulet 2.90
Coral Mithril Amulet 2.90
Spinel Silver Ring 2.90
Spinel Gold Ring 2.90
Spinel Silver Earring 2.90
Spinel Gold Earring 2.90
Spinel Silver Amulet 2.90
Spinel Gold Amulet 2.90
Purple Coral Ring 2.90
Mighty Amulet 2.90
Strong Amulet 2.90
Berserker's Amulet 2.90
Precise Amulet 2.90
Penetrating Amulet 2.90
Carrion Amulet 2.90
Mighty Ring 2.90
Strong Ring 2.90
Penetrating Ring 2.90
Precise Ring 2.90
Berserker's Ring 2.90
Carrion Ring 2.90
Fertilizer Pouch 2.90
Ring of the Catacombs 2.90
Foefire Amulet 2.90
Charged Band 2.90
Carving of Jormag 2.90
Svanir Talisman 2.90
Orrian Artifact Shards 2.90
Amulet of the Dead 2.90
Dissonance Amplifier 2.90
Revolutionary Flyers 2.90
Band of the Moliarchy 2.90
Beetlestone's Band 2.90
Darkened Vine 2.90
Twisted Tendril 2.90
Flame Legion Ritual Book 2.90
Wildfire Talisman 2.90
Passiflora Mithril Ring 2.90
Passiflora Mithril Earring 2.90
Passiflora Mithril Amulet 2.90
Cavalier's Ring 2.90
Magi's Ring 2.90
Rabid Ring 2.90
Soldier's Ring 2.90
Cavalier's Amulet 2.90
Magi's Amulet 2.90
Rabid Amulet 2.90
Soldier's Amulet 2.90
Cavalier's Field Guide 2.90
Soldier's Field Guide 2.90
Book of Rabid Deeds 2.90
Magi's Field Guide 2.90
Snowflake Copper Amulet 2.90
Snowflake Copper Earring 2.90
Snowflake Copper Ring 2.90
Azurite Mithril Amulet 2.90
Azurite Mithril Earring 2.90
Azurite Mithril Ring 2.90
Candy Corn Silver Pendant 2.90
Sunstone Silver Band 2.90
Sunstone Gold Ring 2.90
Opal Platinum Ring 2.90
Opal Mithril Ring 2.90
Sunstone Silver Earring 2.90
Sunstone Gold Earring 2.90
Opal Platinum Earring 2.90
Opal Mithril Earring 2.90
Sunstone Silver Pendant 2.90
Sunstone Gold Amulet 2.90
Opal Platinum Amulet 2.90
Opal Mithril Amulet 2.90
Caudecus's Journal 2.90
Coral Pendant 2.90
Arctic Ring 2.90
Diffraction Crystal 2.90
Jar of Lost Souls 2.90
TOTAL 1,005.89 —
Based on data from api.guildwars2.com
1 Prices in gold

Outliers on Crafting Material

Let’s have a closer look on one of the most important resources in the game: Crafting material.

The base material can be collected everywhere in the world from trees, stones or herbs. As a player, you can learn up to two professions that can improve those base materials to something better and useful.

Let’s first check if there are any outlers in the data that we should clean.

mean_price_crafting_sells <- df_sells %>% 
  filter(type == "CraftingMaterial")

mean_price_crafting_buys <- df_buys %>% 
  filter(type == "CraftingMaterial")

ggplot() +  
  geom_boxplot(data = mean_price_crafting_sells, aes(x = 'Sells', y = unit_price_gold)) +
  geom_boxplot(data = mean_price_crafting_buys, aes(x = 'Buys', y = unit_price_gold)) +
  geom_hline(yintercept = 999, linetype="dashed", color = "red") +
  scale_y_continuous(labels = comma)
## Warning: Removed 42 rows containing non-finite values (stat_boxplot).

We can see that on the buys, there aren’t any outliers because the human brain is still working and no one buys overpriced goods. On the other hand, on the sells there are some people who are exaggerating with the prices. Let’s strip everything at 1000 and above.

df_crafting_material <-  df_sells %>% 
  filter(type == "CraftingMaterial",
         unit_price_gold < 1000) %>% 
  group_by(rarity, name) %>% 
   summarize(
    mean_price = mean(unit_price_gold),
    sum_quantity = sum(quantity),
    picture = web_image(unique(icon), height = 25)
  ) %>% 
  mutate(
    quantity_label = label_number_si(accuracy=0.1)(sum_quantity),
    mean_price_gold = round(mean_price, digits = 3)) %>% 
  arrange(desc(sum_quantity)) %>% 
  select(rarity, name, mean_price_gold, quantity_label, sum_quantity, picture)

df_crafting_material %>% 
  datatable(extensions = c('ColReorder', 'Buttons', 'Responsive', 'Scroller', 'SearchPanes', 'Select'),
            options = list(colReorder = TRUE,
                           dom = 'Bfrtip', 
                           buttons = list('searchPanes', 'copy', list(
                                           extend = 'collection',
                                           buttons = c('csv', 'excel', 'pdf'), 
                                           text = 'Download'))),
            escape = FALSE,
            colnames=c("#", "Rarity", "Name", "Mean price in gold", "Quantity (short)", "Quantity", "Icon")
            )
exp_sold_item_name <- df_crafting_material %>% 
  arrange(desc(mean_price_gold)) %>% 
  ungroup() %>%
  select(name) %>% 
  first() %>% 
  first()

Looking at Crafting Material, we first see that there is an overwhelming amount of offers on the market. There are 32.9M items of Nougat Center! Nougat Center, Chattering Skull and Plastic Fangs are all Halloween Event items and therefore not hard to get but also not very useful.

The first real crafting material is Large Claw, with still over 21.4M items.

The most expensive mean crafting material is Sentinel’s Embroidered Silk Insignia.

Material with the highest profit

First of all, we need to define how many items we want to buy and sell in one batch. Because supply and demand are very different and the price fluctuates for each listing, it makes more sense to set a reasonable default first.

Let’s try with 50 items.

## Take 50 from listings * quantity for each row
## compare with same item on sells
## ...

If possible, it’s always better to sell items on the action house instead of the vendor. -> really?

Search for outliers:

# render boxplot with outliers
item_list_sells %>% 
ggplot(aes(x = factor(rarity), y = unit_price, fill = factor(type))) + 
  geom_boxplot() +
  theme(axis.text.x = element_text(angle = 90, vjust = 0.5, hjust=1))
## Warning: Removed 388 rows containing non-finite values (stat_boxplot).